﻿using System;
using System.Data;
using System.Collections.Generic;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;
//using MySql.Data;
using System.Data.Odbc;
//using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingCoursPersonDAL : ITrainingCoursPersonDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        //private MySqlConnection conn = new MySqlConnection(SystemInfo._ConnectionString);
        private SqlConnection conn = new SqlConnection(SystemInfo._ConnectionString);
        

        public DataTable LoadPerson()
        {
            try
            {
                conn.Open();
                //MySqlCommand cmd = new MySqlCommand();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "hr_sp_traing_course_person_add";
                cmd.CommandType = CommandType.StoredProcedure;

                //MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dtb = new DataTable();
                da.Fill(dtb);

                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable Search(int? EmpSeq, int? EmpName, int? JobName, int? PosName)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT b.emp_seq, CONCAT(d.prefix_name,SPACE(2), b.first_name,SPACE(2),b.last_name) emp_name,"
                        + " c.job_site_name, a.pos_name FROM hrm_mp_positions a"
                        + " INNER JOIN hrm_ps_employees b ON b.pos_seq = a.pos_seq"
                        + " INNER JOIN hrm_mp_job_sites c ON c.job_site_seq = b.working_job_site_seq"
                        + " INNER JOIN mas_name_prefixes d ON b.name_prefix_seq = d.name_prefix_seq "
                        + " WHERE b.record_status not like @status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}